package com.yangtao.framework.plugin.poi;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;

/**
 * 基于POI的excel读取类
 *
 * @auther: 杨涛
 * Date: 11-5-12
 * Time: 下午2:03
 * @version: 1.0
 */
public class ExcelReader {
	/**
	 * 工作薄
	 */
	private Workbook workbook;
	/**
	 * 默认的sheet
	 */
	private Sheet defaultSheet;

	/**
	 * 工作薄下所有的sheet
	 */
	private Sheet[] sheets;
	/**
	 * 工作薄中sheet个数
	 */
	private int numberOfSheets;
	/**
	 * excel文件版本
	 */
	private ExcelVersion excelVersion = ExcelVersion.MS_2003;

	private InputStream _inputStream;

	/**
	 * 构造函数
	 *
	 * @param fileName
	 */

	public ExcelReader(String fileName) throws IOException {
		if (fileName.endsWith(".xlsx")) {
			this.excelVersion = ExcelVersion.MS_2007;
		}
		_inputStream = new FileInputStream(fileName);
		initialize();
	}

	/**
	 * 构造函数
	 *
	 * @param file
	 * @throws java.io.IOException
	 */
	public ExcelReader(File file) throws IOException {
		if (file.getName().endsWith(".xlsx")) {
			this.excelVersion = ExcelVersion.MS_2007;
		}
		this._inputStream = new FileInputStream(file);
		initialize();
	}

	/**
	 * 构造函数
	 *
	 * @param file
	 * @throws java.io.IOException
	 */
	public ExcelReader(File file, ExcelVersion excelVersion) throws IOException {
		this._inputStream = new FileInputStream(file);
		this.excelVersion = excelVersion;
		initialize();
	}

	/**
	 * 构造函数
	 *
	 * @param inputStream
	 * @param excelVersion
	 * @throws java.io.IOException
	 */
	public ExcelReader(InputStream inputStream, ExcelVersion excelVersion) throws IOException {
		this._inputStream = inputStream;
		this.excelVersion = excelVersion;
		initialize();
	}

	/**
	 * 构造函数
	 *
	 * @param inputStream
	 * @throws java.io.IOException
	 */
	public ExcelReader(InputStream inputStream) throws IOException {
		this(inputStream, ExcelVersion.MS_2003);
	}

	/**
	 * 自身初始化
	 *
	 * @throws java.io.IOException
	 */
	private void initialize() throws IOException {
		if (excelVersion == ExcelVersion.MS_2003) {
			workbook = new HSSFWorkbook(_inputStream);
		} else if (excelVersion == ExcelVersion.MS_2007) {
			workbook = new XSSFWorkbook(_inputStream);
		}
		numberOfSheets = workbook.getNumberOfSheets();
		sheets = new Sheet[numberOfSheets];
		for (int y = 0; y < numberOfSheets; y++) {
			sheets[y] = workbook.getSheetAt(y);
		}
		defaultSheet = sheets[0];
	}

	/**
	 * 关闭Stream
	 */
	public void close() {
		if (_inputStream != null) {
			try {
				_inputStream.close();
			} catch (IOException e) {
				_inputStream = null;
			}
		}
	}

	/**
	 * 取得默认sheet的指定单元格的内容
	 *
	 * @param rowIndex
	 * @param cellIndex
	 * @return
	 */
	public String getCellValue(int rowIndex, int cellIndex) {
		return getCellValue(defaultSheet, rowIndex, cellIndex);
	}

	/**
	 * 取得默认sheet的指定单元格的内容
	 *
	 * @param location
	 * @return
	 */
	public String getCellValue(String location) {
		Cell cell = getCell(location);
		return cell.getStringCellValue();
	}

	/**
	 * 取得默认sheet的指定单元格内容
	 *
	 * @param rowIndex
	 * @param cellIndex
	 * @return
	 */
	public Cell getCell(int rowIndex, int cellIndex) {
		return getCell(defaultSheet, rowIndex, cellIndex);
	}

	/**
	 * 取得默认sheet的指定单元格内容
	 *
	 * @param locationArg 位置参数
	 * @return
	 */
	public Cell getCell(String locationArg) {
		return getCell(defaultSheet, locationArg);
	}

	/**
	 * 根据行标取得row
	 *
	 * @param rowIndex 行标
	 * @return
	 */
	public Row getRow(int rowIndex) {
		return getRow(defaultSheet, rowIndex);
	}

	/**
	 * 根据行标取得row
	 *
	 * @param sheet    sheet
	 * @param rowIndex 行标
	 * @return
	 */
	public Row getRow(Sheet sheet, int rowIndex) {
		return sheet.getRow(rowIndex);
	}

	/**
	 * 获取sheet指定序列的单元格的内容
	 *
	 * @param sheet
	 * @param rowIndex
	 * @param cellIndex
	 * @return
	 */
	public String getCellValue(Sheet sheet, int rowIndex, int cellIndex) {
		Cell cell = getCell(sheet, rowIndex, cellIndex);
		return cell.getStringCellValue();
	}

	/**
	 * 根据行列号，取出一个sheet中的单元格
	 *
	 * @param sheet     excel sheet
	 * @param rowIndex  行号
	 * @param cellIndex 列号
	 * @return
	 */
	public Cell getCell(Sheet sheet, int rowIndex, int cellIndex) {
		Row row = sheet.getRow(rowIndex);
		Cell cell = row.getCell(cellIndex);
		return cell;
	}

	/**
	 * 根据一个位置字符串取出一个单元格
	 *
	 * @param sheet       excel sheet
	 * @param locationStr 位置字符串
	 * @return
	 */
	public Cell getCell(Sheet sheet, String locationStr) {
		if (StringUtils.isEmpty(locationStr)) {
			throw new RuntimeException("位置参数不能为空");
		}
		int rowIndex;
		int cellIndex;
		String row = "";
		String cell = "";
		for (int y = 0; y < locationStr.length(); y++) {
			char c = locationStr.charAt(y);
			//遇到第一个数字，就把数字前的值都截断
			if (isNumber(c)) {
				cell = locationStr.substring(0, y);
				row = locationStr.substring(y);
				break;
			}
		}
		rowIndex = Integer.valueOf(row) - 1;
		cellIndex = _26To10(cell) - 1;
		return getCell(sheet, rowIndex, cellIndex);
	}

	/**
	 * 将26进制的数字变成10进制的数字
	 *
	 * @return
	 */
	public static int _26To10(String _26Str) {
		int ret = 0;
		for (int k = 0; k < _26Str.length(); k++) {
			char c = _26Str.charAt(k);
			ret += charToInt(c) * Math.pow(26, _26Str.length() - k - 1);
		}
		return ret;
	}

	public static int charToInt(char c) {
		if (c == 'A') {
			return 1;
		}
		if (c == 'B') {
			return 2;
		}
		if (c == 'C') {
			return 3;
		}
		if (c == 'D') {
			return 4;
		}
		if (c == 'E') {
			return 5;
		}
		if (c == 'F') {
			return 6;
		}
		if (c == 'G') {
			return 7;
		}
		if (c == 'H') {
			return 8;
		}
		if (c == 'I') {
			return 9;
		}
		if (c == 'J') {
			return 10;
		}
		if (c == 'K') {
			return 11;
		}
		if (c == 'L') {
			return 12;
		}
		if (c == 'M') {
			return 13;
		}
		if (c == 'N') {
			return 14;
		}
		if (c == 'O') {
			return 15;
		}
		if (c == 'P') {
			return 16;
		}
		if (c == 'Q') {
			return 17;
		}
		if (c == 'R') {
			return 18;
		}
		if (c == 'S') {
			return 19;
		}
		if (c == 'T') {
			return 20;
		}
		if (c == 'U') {
			return 21;
		}
		if (c == 'V') {
			return 22;
		}
		if (c == 'W') {
			return 23;
		}
		if (c == 'X') {
			return 24;
		}
		if (c == 'Y') {
			return 25;
		}
		if (c == 'Z') {
			return 26;
		}
		return 0;
	}

	private static boolean isNumber(char c) {
		if (c == '0') {
			return true;
		}
		if (c == '1') {
			return true;
		}
		if (c == '2') {
			return true;
		}
		if (c == '3') {
			return true;
		}
		if (c == '4') {
			return true;
		}
		if (c == '5') {
			return true;
		}
		if (c == '6') {
			return true;
		}
		if (c == '7') {
			return true;
		}
		if (c == '8') {
			return true;
		}
		if (c == '9') {
			return true;
		}
		return false;
	}

	/**
	 * 内部测试方法
	 *
	 * @param args
	 */
	public static void main(String[] args) {
		String locationStr = "D12";
		String row = "";
		String cell = "";
		for (int i = 0; i < locationStr.length(); i++) {
			char c = locationStr.charAt(i);
			//遇到第一个数字，就把数字前的值都截断
			if (isNumber(c)) {
				cell = locationStr.substring(0, i);
				row = locationStr.substring(i);
				break;
			}
		}
		int rowIndex = Integer.valueOf(row);
		int cellIndex = _26To10(cell);
		System.out.println(rowIndex + "|" + cellIndex);
	}

	public Sheet getDefaultSheet() {
		return defaultSheet;
	}

	public void setDefaultSheet(Sheet defaultSheet) {
		this.defaultSheet = defaultSheet;
	}

	public Workbook getWorkbook() {
		return workbook;
	}

	public void setWorkbook(Workbook workbook) {
		this.workbook = workbook;
	}

	public ExcelVersion getExcelVersion() {
		return excelVersion;
	}

	public void setExcelVersion(ExcelVersion excelVersion) {
		this.excelVersion = excelVersion;
	}
}
